﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.GERP.GHRMS.DALFactory;
using KPIS.DBM;


namespace KPIS.GERP.GHRMS.BLL
{
    public class InquireBLL
    {
       // private static readonly IInquireDAL DAL = DataAccess.CreateInquire();
        // private InquireInfo inquireInfo = new InquireInfo();

        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        public DataSet ListInquire()
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from HRM_RC_INQUIRE_SUBJECTS WHERE RECORD_STATUS=@RecordStatus");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }



        public DataTable ListInquire(int year, string emp_type_seq, string pos_type_seq, string class_seq
            , string edu_qual_seq, string edu_major_seq, string edu_ins_seq, string old_job_site
            , string request_job_site)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string strSQL;
                strSQL = "SELECT DISTINCT CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS FULL_NAME"
                        + ", eid.INQUIRE_DTLS_SEQ, eid.SEQ, eid.REASON, p.POS_NAME, js.JOB_SITE_NAME AS OLD_JOB_SITE"
                        + ", js2.JOB_SITE_NAME AS REQUEST_JOB_SITE"
                        + ", eid.INQUIRE_SUBJECT_SEQ, ris.SUBJ_NO, ris.DESCR"
                    + " FROM HRM_ES_INQUIRES ei"
                        + " LEFT JOIN HRM_ES_INQUIRE_DTLS eid ON ei.INQUIRE_SEQ = eid.INQUIRE_SEQ"
                        + " LEFT JOIN HRM_RC_INQUIRE_SUBJECTS ris ON eid.INQUIRE_SUBJECT_SEQ = ris.SUBJ_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON ei.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN HRM_PS_EMP_EDUCATIONS ee ON e.EMP_SEQ = ee.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS fp ON p.FIELD_POS_SEQ = fp.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES js ON p.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES js2 ON eid.JOB_SITE_SEQ = js2.JOB_SITE_SEQ"
                    + " WHERE ei.RECORD_STATUS not like @DeleteRecord"
                    + " AND e.RECORD_STATUS = @ActiveRecord";

                if (emp_type_seq != "")
                    strSQL += " and e.emp_type_seq = " + emp_type_seq;
                if (pos_type_seq != "")
                    strSQL += " and fp.pos_type_seq = " + pos_type_seq;
                if (class_seq != "")
                    strSQL += " and e.class_seq = " + class_seq;
                if (edu_qual_seq != "")
                    strSQL += " and ee.edu_qual_seq in (" + edu_qual_seq + ")";
                if (edu_major_seq != "")
                    strSQL += " and ee.edu_major_seq in (" + edu_major_seq + ")";
                if (edu_ins_seq != "")
                    strSQL += " and ee.edu_ins_seq in (" + edu_ins_seq + ")";
                if (old_job_site != "")
                    strSQL += " and p.job_site_seq = " + old_job_site;
                if (request_job_site != "")
                    strSQL += " and eid.job_site_seq = " + request_job_site;
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public bool ChkDuplicate(string subNo)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from HRM_RC_INQUIRE_SUBJECTS where subj_no = " + subNo + " and record_status = '" + SystemInfo._ActiveRecordStatus + "'");
            if (ds.Tables[0].Rows.Count != 0)
                return true;
            else
                return false;
        }
        public DataTable ListInqSubject()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                string sql;
                sql = "SELECT SUBJ_SEQ AS ID,DESCR"
                    + " FROM HRM_RC_INQUIRE_SUBJECTS"
                    + " WHERE RECORD_STATUS = @ActiveRecord";

                DataSet ds = new DataSet();
                DataTable dt;                
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch
            {
                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Insert(string subNo, string title, int max, string remarks, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@subNo", subNo);
                dbManager.AddParameters(1, "@title", title);
                dbManager.AddParameters(2, "@max", max);
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "INSERT INTO HRM_RC_INQUIRE_SUBJECTS (subj_no, descr, max,remarks,RECORD_STATUS,created_by,created_when) VALUES (@subNo, @title, @max,@remarks,@RecordStatus,@CreatedBy,@CreatedWhen)");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void Update(int Sequence, string subNo, string title, int max, string remarks, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@subNo", subNo);
                dbManager.AddParameters(2, "@title", title);
                dbManager.AddParameters(3, "@max", max);
                dbManager.AddParameters(4, "@remarks", remarks);
                dbManager.AddParameters(5, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@updated_by", UpdateBy);
                dbManager.AddParameters(7, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE HRM_RC_INQUIRE_SUBJECTS SET subj_no=@subNo, descr=@title,max=@max,remarks=@remarks,updated_by=@updated_by,updated_when=@updated_when WHERE subj_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void Delete(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE HRM_RC_INQUIRE_SUBJECTS SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE subj_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        
    }
}
